import pickle

import openpyxl
from openpyxl.utils import get_column_letter

import tools_pmc

wb = openpyxl.load_workbook(tools_pmc.lj_qlxxb_原)
ws = wb.worksheets[0]

dict_生产试制 = {}
dict_改制 = {}
for i in range(2, ws.max_row + 1):
    if ws.cell(row=i, column=1).value is not None:
        if ws.cell(row=i, column=5).value[0:4] == '5101' or ws.cell(row=i, column=5).value[0:4] == '5105':
            if ws.cell(row=i, column=1).value not in dict_生产试制:
                dict_生产试制[ws.cell(row=i, column=1).value] = ws.cell(row=i, column=6).value
            else:
                dict_生产试制[ws.cell(row=i, column=1).value] = dict_生产试制[ws.cell(row=i, column=1).value] + ws.cell(
                    row=i,
                    column=6).value
        elif ws.cell(row=i, column=5).value[0:4] == '5103' or ws.cell(row=i, column=5).value[0:4] == '5201':
            if ws.cell(row=i, column=1).value not in dict_改制:
                dict_改制[ws.cell(row=i, column=1).value] = ws.cell(row=i, column=6).value
            else:
                dict_改制[ws.cell(row=i, column=1).value] = dict_改制[ws.cell(row=i, column=1).value] + ws.cell(row=i,
                                                                                                                column=6).value

with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\生产试制欠料.pickle", "wb") as f:
    pickle.dump(dict_生产试制, f)
with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\改制欠料.pickle", "wb") as f:
    pickle.dump(dict_改制, f)

ws1 = wb.worksheets[1]
set1 = set(dict_生产试制.keys())
set2 = set(dict_改制.keys())
set1.update(set2)
ws1.append(['品号', "欠料合计", '生产欠料', '改制欠料'])
for i in set1:
    ws1.append([i, dict_生产试制.get(i, 0) + dict_改制.get(i, 0), dict_生产试制.get(i, 0), dict_改制.get(i, 0)])

wb_gdwcqk = openpyxl.load_workbook(tools_pmc.lj_gdwcqk_y)
ws_gdwcqk = wb_gdwcqk.active
ws_gdwcqk.insert_cols(3)
for i in range(2, ws_gdwcqk.max_row + 1):
    ws_gdwcqk.cell(i, 3).value = ws_gdwcqk.cell(i, 1).value + "-" + ws_gdwcqk.cell(i, 2).value
table_array_gdwcqk = list(ws_gdwcqk['C1':'H' + str(ws_gdwcqk.max_row)])

dict_bom_all = tools_pmc.Y状态BOM尾阶用量_pro()[1]


def vlookup(lookup_value, table_array_gdwcqk, col_index_num):
    global a
    for row in table_array_gdwcqk:
        if row[0].value == lookup_value:
            a = table_array_gdwcqk.index(row) - 1
            return row[col_index_num - 1].value, table_array_gdwcqk.index(row) + 1


def 工单对应成品信息拉取(工单号):
    品号, 工单信息所在行 = vlookup(工单号, table_array_gdwcqk, 2)
    list_gdxx = [4, 5, 6, 8]
    if 品号[0] == "3" or 品号[0] == "1":
        for ii in range(0, 4):
            ws.cell(i, ii + 7).value = ws_gdwcqk.cell(工单信息所在行, list_gdxx[ii]).value
    else:
        while table_array_gdwcqk[工单信息所在行-1][1].value[0] != '3':
            工单信息所在行 -= 1
        品号 = table_array_gdwcqk[工单信息所在行-1][1].value
        if ws.cell(i, 1).value in dict_bom_all[品号]:
            for ii in range(0, 4):
                ws.cell(i, ii + 7).value = ws_gdwcqk.cell(工单信息所在行, list_gdxx[ii]).value
        else:
            ws.cell(i, 7).value = "BOM检测未通过，请手动查询！"


for i in range(2, ws.max_row + 1):
    try:
        工单对应成品信息拉取(ws.cell(row=i, column=5).value)
    except:
        pass

ws.cell(row=1, column=7).value = "成品品号"
ws.cell(row=1, column=8).value = "成品品名"
ws.cell(row=1, column=9).value = "成品规格"
ws.cell(row=1, column=10).value = "预计产量"
list1 = [14, 30, 30, 10, 20, 10, 14, 30, 30, 10]
for i in range(1, 11):
    ws.column_dimensions[get_column_letter(i)].width = list1[i - 1]
    ws.cell(1, i).fill = tools_pmc.fill_blue

for i in range(7, 11):
    ws.cell(1, i).fill = tools_pmc.fill_orange

ws.title = "欠料信息表"
ws1.title = "欠料信息汇总"
wb.remove(wb.worksheets[2])

wb.save(tools_pmc.lj_qlxxb)
